package storage

import (
    "fmt"
    "spam3uk/internal/models"
    "time"

    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

// MySQLStorage MySQL存储实现
type MySQLStorage struct {
	db *gorm.DB
}

// NewMySQLStorage 创建MySQL存储实例
func NewMySQLStorage(db *gorm.DB) VehicleStorage {
	return &MySQLStorage{db: db}
}

// GetAllCars 获取所有汽车信息，支持筛选和分页
func (s *MySQLStorage) GetAllCars(filter *models.VehicleFilter) ([]models.VehicleInfo, int64, error) {
	var vehicles []models.VehicleInfo
	var total int64

	query := s.db.Model(&models.VehicleInfo{})

	// 应用筛选条件
	if filter != nil {
		if filter.VehicleCategory != "" {
			query = query.Where("vehicle_category = ?", filter.VehicleCategory)
		}
		if filter.VehicleType != "" {
			query = query.Where("vehicle_type = ?", filter.VehicleType)
		}
		if filter.FordGroup != "" {
			query = query.Where("ford_group = ?", filter.FordGroup)
		}
		if filter.Make != "" {
			query = query.Where("make = ?", filter.Make)
		}
		if filter.Model != "" {
			query = query.Where("model LIKE ?", "%"+filter.Model+"%")
		}
		if filter.MinPrice > 0 {
			query = query.Where("price_from >= ?", filter.MinPrice)
		}
		if filter.MaxPrice > 0 {
			query = query.Where("price_to <= ?", filter.MaxPrice)
		}
		if filter.FuelType != "" {
			query = query.Where("fuel_type = ?", filter.FuelType)
		}
	}

	// 统计总数
	if err := query.Count(&total).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to count vehicles: %w", err)
	}

	// 应用分页
	if filter != nil && filter.Page > 0 && filter.PageSize > 0 {
		offset := (filter.Page - 1) * filter.PageSize
		query = query.Offset(offset).Limit(filter.PageSize)
	}

	// 查询数据，按ID排序
	if err := query.Order("id DESC").Find(&vehicles).Error; err != nil {
		return nil, 0, fmt.Errorf("failed to get vehicles: %w", err)
	}

	return vehicles, total, nil
}

// GetByID 根据ID获取车辆信息
func (s *MySQLStorage) GetByID(id uint) (*models.VehicleInfo, error) {
	var vehicle models.VehicleInfo
	if err := s.db.First(&vehicle, id).Error; err != nil {
		if err == gorm.ErrRecordNotFound {
			return nil, fmt.Errorf("vehicle with id %d not found", id)
		}
		return nil, fmt.Errorf("failed to get vehicle by id: %w", err)
	}
	return &vehicle, nil
}

// Create 创建新的车辆信息
func (s *MySQLStorage) Create(vehicle *models.VehicleInfo) error {
	if err := s.db.Create(vehicle).Error; err != nil {
		return fmt.Errorf("failed to create vehicle: %w", err)
	}
	return nil
}

// Update 更新车辆信息
func (s *MySQLStorage) Update(id uint, vehicle *models.VehicleInfo) error {
	if err := s.db.Model(&models.VehicleInfo{}).Where("id = ?", id).Updates(vehicle).Error; err != nil {
		return fmt.Errorf("failed to update vehicle: %w", err)
	}
	return nil
}

// Delete 删除车辆信息
func (s *MySQLStorage) Delete(id uint) error {
	if err := s.db.Delete(&models.VehicleInfo{}, id).Error; err != nil {
		return fmt.Errorf("failed to delete vehicle: %w", err)
	}
	return nil
}

// GetByType 根据车辆类型获取车辆信息
func (s *MySQLStorage) GetByType(vehicleType string) ([]models.VehicleInfo, error) {
	var vehicles []models.VehicleInfo
	if err := s.db.Where("vehicle_type = ?", vehicleType).Find(&vehicles).Error; err != nil {
		return nil, fmt.Errorf("failed to get vehicles by type: %w", err)
	}
	return vehicles, nil
}

// GetByCategory 根据Ford分类获取车辆信息
func (s *MySQLStorage) GetByCategory(category string) ([]models.VehicleInfo, error) {
	var vehicles []models.VehicleInfo
	if err := s.db.Where("vehicle_category = ?", category).Find(&vehicles).Error; err != nil {
		return nil, fmt.Errorf("failed to get vehicles by category: %w", err)
	}
	return vehicles, nil
}

// GetCategoryStats 获取分类统计信息
func (s *MySQLStorage) GetCategoryStats() (map[string]int64, error) {
	stats := make(map[string]int64)

	categories := models.GetFordCategories()
	for _, cat := range categories {
		var count int64
		if err := s.db.Model(&models.VehicleInfo{}).Where("vehicle_category = ?", cat.Category).Count(&count).Error; err != nil {
			return nil, fmt.Errorf("failed to count vehicles for category %s: %w", cat.Category, err)
		}
		stats[cat.Category] = count
	}

	return stats, nil
}

// GetVehicleDetailURL 根据model名称获取detail_url
func (s *MySQLStorage) GetVehicleDetailURL(modelName string) (string, error) {
    var vehicle models.VehicleInfo
    // 放宽匹配以容忍大小写/空格差异
    err := s.db.Where("LOWER(model) = LOWER(?)", modelName).First(&vehicle).Error
	if err != nil {
		return "", fmt.Errorf("model '%s' not found: %w", modelName, err)
	}
	return vehicle.DetailURL, nil
}

// UpsertVehicle 插入或更新车辆信息 (基于make, model, variant唯一性)
func (s *MySQLStorage) UpsertVehicle(vehicle *models.VehicleInfo) error {
	// 设置抓取时间
	vehicle.ScrapedAt = time.Now()

	// 使用GORM的ON DUPLICATE KEY UPDATE功能
	return s.db.Clauses(clause.OnConflict{
		Columns: []clause.Column{
			{Name: "make"},
			{Name: "model"},
			{Name: "variant"},
		},
		DoUpdates: clause.Assignments(map[string]interface{}{
			"updated_at": time.Now(),
			"scraped_at": time.Now(),
			"created_at": clause.Expr{SQL: "IFNULL(created_at, ?)", Vars: []interface{}{time.Now()}},
			// 更新业务字段
			"ford_group":       clause.Column{Table: "excluded", Name: "ford_group"},
			"vehicle_category": clause.Column{Table: "excluded", Name: "vehicle_category"},
			"vehicle_type":     clause.Column{Table: "excluded", Name: "vehicle_type"},
			"price_from":       clause.Column{Table: "excluded", Name: "price_from"},
			"price_to":         clause.Column{Table: "excluded", Name: "price_to"},
			"fuel_type":        clause.Column{Table: "excluded", Name: "fuel_type"},
			"engine_size":      clause.Column{Table: "excluded", Name: "engine_size"},
			"transmission":     clause.Column{Table: "excluded", Name: "transmission"},
			"body_style":       clause.Column{Table: "excluded", Name: "body_style"},
			"doors":            clause.Column{Table: "excluded", Name: "doors"},
			"seats":            clause.Column{Table: "excluded", Name: "seats"},
			"image_url":        clause.Column{Table: "excluded", Name: "image_url"},
			"detail_url":       clause.Column{Table: "excluded", Name: "detail_url"},
			"trim_detail_link": clause.Column{Table: "excluded", Name: "trim_detail_link"},
			"description":      clause.Column{Table: "excluded", Name: "description"},
			"specifications":   clause.Column{Table: "excluded", Name: "specifications"},
			"features":         clause.Column{Table: "excluded", Name: "features"},
			"availability":     clause.Column{Table: "excluded", Name: "availability"},
			"source_page":      clause.Column{Table: "excluded", Name: "source_page"},
		}),
	}).Create(vehicle).Error
}
